# 重新执行，因为之前的代码环境已重置
import pandas as pd
import re
from collections import defaultdict, Counter

# 重新加载上传的文件
file_path = "/Users/lhd/Downloads/319505077_按序号_模糊综合评价法燃气管道泄漏风险调查问卷_3_2.xlsx"
xls = pd.ExcelFile(file_path)
df = xls.parse(xls.sheet_names[0])

# 查找“总分”列的位置，从它后面开始处理
score_start_index = df.columns.get_loc("总分") + 1
score_df = df.iloc[:, score_start_index:]

# 构建结构：{一级指标: {二级指标: {打分值: 比例}}}
probability_matrix = defaultdict(dict)

for col in score_df.columns:
    col_str = str(col).strip()
    match = re.match(r"(.+?)—(.+)", col_str)
    if match:
        primary, secondary = match.groups()
    else:
        primary = col_str
        secondary = col_str

    # 统计每个打分值出现次数
    counts = Counter(score_df[col])
    total = sum(counts.values())

    # 生成分值 -> 概率向量
    value_prob = {}
    for score in ['1', '2', '3', '4', '5']:
        value_prob[score] = round(counts.get(int(score), 0) / total, 4)

    probability_matrix[primary][secondary] = value_prob

# 转换为嵌套结构 DataFrame 预览
flat_data = []
for primary, subdict in probability_matrix.items():
    for secondary, probs in subdict.items():
        row = {"一级指标": primary, "二级指标": secondary}
        row.update(probs)
        flat_data.append(row)

df_result = pd.DataFrame(flat_data)
print(df_result)

# ==== 保存 probability_matrix 为 JSON 文件 ====
import os
import json

# 1. 目录路径：项目根目录下的 “数据” 文件夹
project_root = os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))
out_dir = os.path.join(project_root, "数据")
os.makedirs(out_dir, exist_ok=True)

# 2. 构建 {二级指标: {打分: 概率}} 结构（去掉一级标签）
probability_matrix_json = {}
for primary_dict in probability_matrix.values():
    for secondary, probs in primary_dict.items():
        probability_matrix_json[secondary] = {str(k): v for k, v in probs.items()}

# 3. 保存为 JSON 文件
output_path = os.path.join(out_dir, "模糊打分概率矩阵.json")
with open(output_path, "w", encoding="utf-8") as f:
    json.dump(probability_matrix_json, f, ensure_ascii=False, indent=2)